﻿﻿using System;
﻿using System.Collections.ObjectModel;
﻿using System.Linq;
using System.Data;
using System.Data.SqlClient;
using Pantry.Commons;
using Pantry.Entity;


namespace Pantry.DataAccess
{
    public sealed class ContactDao : IDisposable 
    {
        private readonly SqlConnection _sqlConnection;

        /// <summary>
        /// Initializes a new instance of the <see cref="ContactDao"/> class.
        /// </summary>
        public ContactDao()
        {
            _sqlConnection = SqlConnectionTool.SqlGetConnection();
        }

        public ContactDao(string connectionString)
        {
            _sqlConnection = new SqlConnection(connectionString);
        }


        /// <summary>
        /// Gets the list contact.
        /// </summary>
        /// <returns></returns>
        public DataTable ListContact()
        {
            try
            {
                DataSet dataSet = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text,
                                                           DBSchemas.ListAllContact);
                return dataSet.Tables[0];
            }
            catch (Exception exception)
            {
                ExceptionHandler.WriteErrorLog(exception);
                throw;
            }
        }

        /// <summary>
        /// Gets the list contact object.
        /// </summary>
        /// <returns></returns>
        public Collection<Contact> ListContactObject()
        {
            try
            {
                DataSet dataSet = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, DBSchemas.ListAllContact);
                DataTable dataTable = dataSet.Tables[0];
                return new Collection<Contact>((from DataRow dataRow in dataTable.Rows
                                       select new Contact()
                                                  {
                                                      ContactId = (int)dataRow["ContactId"],
                                                      FirstName = (string)dataRow["FirstName"],
                                                      Surname = (string)dataRow["SurName"],
                                                      KnownAs = (string)dataRow["KnownAs"],
                                                      OfficePhone = (string)dataRow["OfficePhone"],
                                                      MobilePhone = (string)dataRow["MobilePhone"],
                                                      HomePhone = (string)dataRow["HomePhone"],
                                                      Email = (string)dataRow["Email"],
                                                      ManagerId = (int)dataRow["ManageId"],
                                                      ContactType = (int)dataRow["ContactTypeId"],
                                                      BestContactMethod = (int)dataRow["BestContactMethod"],
                                                      JobRole = (string)dataRow["JobRole"],
                                                      WorkBase = (string)dataRow["Workbase"],
                                                      JobTitle = (string)dataRow["JobTitle"],
                                                      IsActive = (bool)dataRow["IsActive"]
                                                  }).ToList());
            }
            catch (Exception exception)
            {
                ExceptionHandler.WriteErrorLog(exception);
                throw;
            }
        }

        /// <summary>
        /// Gets the list active contact.
        /// </summary>
        /// <returns></returns>
        public DataTable ListActiveContact()
        {
            try
            {
                DataSet dataSet = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text,
                                                           DBSchemas.ListActiveContact);
                return dataSet.Tables[0];
            }
            catch (Exception exception)
            {
                ExceptionHandler.WriteErrorLog(exception);
                throw;
            }
        }
        /// <summary>
        /// Gets the list active contact object.
        /// </summary>
        /// <returns></returns>
        public Collection<Contact> ListActiveContactObject()
        {
            try
            {
                DataSet dataSet = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, DBSchemas.ListActiveContact);
                DataTable dataTable = dataSet.Tables[0];
                return new Collection<Contact>((from DataRow dataRow in dataTable.Rows
                                       select new Contact()
                                                  {
                                                      ContactId = (int)dataRow["ContactId"],
                                                      FirstName = (string)dataRow["FirstName"],
                                                      Surname = (string)dataRow["SurName"],
                                                      KnownAs = (string)dataRow["KnownAs"],
                                                      OfficePhone = (string)dataRow["OfficePhone"],
                                                      MobilePhone = (string)dataRow["MobilePhone"],
                                                      HomePhone = (string)dataRow["HomePhone"],
                                                      Email = (string)dataRow["Email"],
                                                      ManagerId = (int)dataRow["ManageId"],
                                                      ContactType = (int)dataRow["ContactTypeId"],
                                                      BestContactMethod = (int)dataRow["BestContactMethodId"],
                                                      JobRole = (string)dataRow["JobRole"],
                                                      WorkBase = (string)dataRow["Workbase"],
                                                      JobTitle = (string)dataRow["JobTitle"],
                                                      IsActive = (bool)dataRow["IsActive"]
                                                  }).ToList());
            }
            catch (Exception exception)
            {
                ExceptionHandler.WriteErrorLog(exception);
                throw;
            }
        }

        public Contact ContactObjectById(int contactId)
        {
            try
            {
                var parameter = new SqlParameter("@ContactId", SqlDbType.Int) {Value = contactId};
                var dataSet = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text,
                                                           DBSchemas.GetContactById, parameter);
                var dataRow = dataSet.Tables[0].Rows[0];

                var contact = new Contact
                                      {
                                          ContactId = (int) dataRow["ContactId"],
                                          FirstName = (string) dataRow["FirstName"],
                                          Surname = (string) dataRow["SurName"],
                                          KnownAs = (string) dataRow["KnownAs"],
                                          OfficePhone = (string) dataRow["OfficePhone"],
                                          MobilePhone = (string) dataRow["MobilePhone"],
                                          HomePhone = (string) dataRow["HomePhone"],
                                          Email = (string) dataRow["Email"],
                                          ManagerId = (int) dataRow["ManagerId"],
                                          ContactType = (int) dataRow["ContactTypeId"],
                                          BestContactMethod = (int) dataRow["BestContactMethodId"],
                                          JobRole = (string) dataRow["JobRole"],
                                          WorkBase = (string) dataRow["Workbase"],
                                          JobTitle = (string) dataRow["JobTitle"],
                                          IsActive = (bool) dataRow["IsActive"]
                                      };
                return contact;
            }
            catch (Exception exception)
            {
                ExceptionHandler.WriteErrorLog(exception);
                throw;
            }

        }
        /// <summary>
        /// Gets the manager name by id.
        /// </summary>
        /// <param name="managerId">The manager id.</param>
        /// <returns></returns>
        public string ManagerNameById(int managerId)
        {
            try
            {
                var parameter = new SqlParameter("@ContactId", SqlDbType.Int) {Value = managerId};
                return
                    (string)
                    SqlHelper.ExecuteScalar(_sqlConnection, CommandType.Text, DBSchemas.GetManagerName,
                                            parameter);
            }
            catch (Exception)
            {
                
                throw;
            }
        }
        /// <summary>
        /// Creates the new contact.
        /// </summary>
        /// <param name="contact">The contact.</param>
        /// <returns></returns>
        public int CreateNewContact(Contact contact)
        {
            try
            {
                SqlParameter[] parameters = new SqlParameter[14];
                parameters[0] = new SqlParameter("@FirstName", SqlDbType.VarChar, 50) { Value = contact.FirstName };
                parameters[1] = new SqlParameter("@SurName", SqlDbType.VarChar, 50) { Value = contact.Surname };
                parameters[2] = new SqlParameter("@KnownAs", SqlDbType.VarChar, 50) { Value = contact.KnownAs };
                parameters[3] = new SqlParameter("@OfficePhone", SqlDbType.VarChar, 50) { Value = contact.OfficePhone };
                parameters[4] = new SqlParameter("@MobilePhone", SqlDbType.VarChar, 50) { Value = contact.MobilePhone };
                parameters[5] = new SqlParameter("@HomePhone", SqlDbType.VarChar, 50) { Value = contact.HomePhone };
                parameters[6] = new SqlParameter("@Email", SqlDbType.VarChar, 50) { Value = contact.Email };
                parameters[7] = new SqlParameter("@ManagerId", SqlDbType.Int) { Value = contact.ManagerId };
                parameters[8] = new SqlParameter("@ContactTypeId", SqlDbType.Int) { Value = contact.ContactType };
                parameters[9] = new SqlParameter("@BestContactMethodId", SqlDbType.Int) { Value = contact.BestContactMethod };
                parameters[10] = new SqlParameter("@JobRole", SqlDbType.VarChar, 50) { Value = contact.JobRole };
                parameters[11] = new SqlParameter("@Workbase", SqlDbType.VarChar, 50) { Value = contact.WorkBase };
                parameters[12] = new SqlParameter("@JobTitle", SqlDbType.VarChar, 50) { Value = contact.JobTitle };
                parameters[13] = new SqlParameter("@IsActive", SqlDbType.Int) { Value = contact.IsActive };
                return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, DBSchemas.CreateNewContact, parameters);
            }
            catch (Exception exception)
            {
                ExceptionHandler.WriteErrorLog(exception);
                throw;
            }
        }

        /// <summary>
        /// Updates the contact.
        /// </summary>
        /// <param name="contact">The contact.</param>
        /// <returns></returns>
        public int UpdateContact(Contact contact)
        {
            try
            {
                SqlParameter[] parameters = new SqlParameter[15];
                parameters[0] = new SqlParameter("@FirstName", SqlDbType.VarChar, 50) { Value = contact.FirstName };
                parameters[1] = new SqlParameter("@SurName", SqlDbType.VarChar, 50) { Value = contact.Surname };
                parameters[2] = new SqlParameter("@KnownAs", SqlDbType.VarChar, 50) { Value = contact.KnownAs };
                parameters[3] = new SqlParameter("@OfficePhone", SqlDbType.VarChar, 50) { Value = contact.OfficePhone };
                parameters[4] = new SqlParameter("@MobilePhone", SqlDbType.VarChar, 50) { Value = contact.MobilePhone };
                parameters[5] = new SqlParameter("@HomePhone", SqlDbType.VarChar, 50) { Value = contact.HomePhone };
                parameters[6] = new SqlParameter("@Email", SqlDbType.VarChar, 50) { Value = contact.Email };
                parameters[7] = new SqlParameter("@ManagerId", SqlDbType.Int) { Value = contact.ManagerId };
                parameters[8] = new SqlParameter("@ContactTypeId", SqlDbType.Int) { Value = contact.ContactType };
                parameters[9] = new SqlParameter("@BestContactMethodId", SqlDbType.Int) { Value = contact.BestContactMethod };
                parameters[10] = new SqlParameter("@JobRole", SqlDbType.VarChar, 50) { Value = contact.JobRole };
                parameters[11] = new SqlParameter("@Workbase", SqlDbType.VarChar, 50) { Value = contact.WorkBase };
                parameters[12] = new SqlParameter("@JobTitle", SqlDbType.VarChar, 50) { Value = contact.JobTitle };
                parameters[13] = new SqlParameter("@IsActive", SqlDbType.Bit) { Value = contact.IsActive };
                parameters[14] = new SqlParameter("@ContactId", SqlDbType.Int) { Value = contact.ContactId };
                return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, DBSchemas.UpdateContact, parameters);

            }
            catch (Exception exception)
            {
                ExceptionHandler.WriteErrorLog(exception);
                throw;
            }
        }

        /// <summary>
        /// Checks the user is exists.
        /// </summary>
        /// <param name="mobilePhone">The mobile phone.</param>
        /// <param name="email">The email.</param>
        /// <returns></returns>
        public int CheckUserIsExists(string mobilePhone, string email)
        {
            try
            {
                SqlParameter[] parameters = new SqlParameter[2];
                parameters[0] = new SqlParameter("@MobilePhone", SqlDbType.NVarChar, 50) { Value = mobilePhone };
                parameters[1] = new SqlParameter("@Email", SqlDbType.NVarChar, 50) { Value = email };
                return
                    (int)
                    SqlHelper.ExecuteScalar(_sqlConnection, CommandType.Text, DBSchemas.CheckUserIsExists,
                                            parameters);
            }
            catch (Exception)
            {
                
                throw;
            }
        }

        public DataTable ListContactType()
        {
            try
            {
                DataSet dataSet = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text,
                                                           DBSchemas.ListContactType);
                return dataSet.Tables[0];
            }
            catch (Exception)
            {
                
                throw;
            }
        }

        public DataTable ListBestContactMethod()
        {
            try
            {
                DataSet dataSet = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text,
                                                           DBSchemas.ListBestContactMethods);
                return dataSet.Tables[0];
            }
            catch (Exception)
            {
                
                throw;
            }
        }


        private void Dispose(bool disposing)
        {
            if (disposing)
            {
                _sqlConnection.Close();
            }
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
    }
}